package org.JDBC;

import org.junit.Test;

import java.sql.*;

public class DMLTest {
    private String driverClass = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://127.0.0.1:3306/briup";
    private String user = "root";
    private String password = "1234";

    //插入数据
    @Test
    public void insert() {
        Connection conn = null;
        PreparedStatement stmt = null; // 使用PreparedStatement
        try {
            //1.加载注册驱动
            Class.forName(driverClass);
            //2.获取连接对象
            conn = DriverManager.getConnection(url,user,password);
            //3.获取PreparedStatement对象
            String sql = "insert into t_user(id,name,age) values(?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setLong(1, 1);
            stmt.setString(2, "tom");
            stmt.setInt(3, 20);
            //4.执行SQL语句
            int rows = stmt.executeUpdate();
            //5.处理结果
            System.out.println("运行结果 rows：" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    //更新数据
    @Test
    public void update() {
        Connection conn = null;
        PreparedStatement stmt = null; // 使用PreparedStatement
        try {
            //1.加载注册驱动
            Class.forName(driverClass);
            //2.获取连接对象
            conn = DriverManager.getConnection(url,user,password);
            //3.获取PreparedStatement对象
            String sql = "update t_user set name=? where id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, "mary");
            stmt.setLong(2, 1);
            //4.执行SQL语句
            int rows = stmt.executeUpdate();
            //5.处理结果
            System.out.println("运行结果 rows：" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    // 删除数据
    @Test
    public void delete() {
        Connection conn = null;
        PreparedStatement stmt = null; // 使用PreparedStatement
        try {
            //1.加载注册驱动
            Class.forName(driverClass);
            //2.获取连接对象
            conn = DriverManager.getConnection(url, user, password);
            //3.获取PreparedStatement对象
            String sql = "delete from t_user where id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setLong(1, 1);
            //4.执行SQL语句
            int rows = stmt.executeUpdate();
            //5.处理结果
            System.out.println("运行结果 rows：" + rows);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    // 批量插入数据
    @Test
    public void batchInsert() {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            //1.加载注册驱动
            Class.forName(driverClass);
            //2.获取连接对象
            conn = DriverManager.getConnection(url,user,password);
            //3.获取PreparedStatement对象
            String sql = "insert into t_user(id,name,age) values(?,?,?)";
            stmt = conn.prepareStatement(sql);
            
            // 添加多条记录
            stmt.setLong(1, 2);
            stmt.setString(2, "alice");
            stmt.setInt(3, 25);
            stmt.addBatch();
            
            stmt.setLong(1, 3);
            stmt.setString(2, "bob");
            stmt.setInt(3, 30);
            stmt.addBatch();
            
            //4.执行批处理
            int[] rows = stmt.executeBatch();
            //5.处理结果
            System.out.println("批量插入结果 rows：" + java.util.Arrays.toString(rows));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}